package br.skytef.stmanager.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.skytef.stmanager.entities.ControlPlanilhasEntity;
import br.skytef.stmanager.utils.Pool;

public class ControlPlanilhaDAO {

	private static final String INSERT = "INSERT INTO controlPlanilha(idcontrolPlanilha, nomePlanilha, linhasImportadas, erros, dataImportacao, licencaNumLoja, totalFaturado, totalSE, totalLyra, totalGSurf) VALUES (?,?,?,?,?,?,?,?,?,?);";
	private static final String UPDATE = "UPDATE controlPlanilha SET nomePlanilha=?, linhasImportadas=?, erros=?, dataImportacao=?, licencaNumLoja=?, totalFaturado=?, totalSE=?, totalLyra=?, totalGSurf=? "
			+ "WHERE idcontrolPlanilha=?;";
	private static Connection conexao = null;

	public boolean inserirAtualizar(ControlPlanilhasEntity controle){
		try {
			if(buscar(controle.getIdcontrolPlanilha())==null){
				conexao = Pool.getConnection();
				PreparedStatement preparedStmt;
				preparedStmt = conexao.prepareStatement(INSERT);
				preparedStmt.setInt(1, controle.getIdcontrolPlanilha());
				preparedStmt.setString(2, controle.getNomePlanilha());
				preparedStmt.setInt(3, controle.getLinhasImportadas());
				preparedStmt.setInt(4, controle.getErros());
				preparedStmt.setTimestamp(5, controle.getDataImportacao());
				preparedStmt.setInt(6, controle.getLicencaNumLoja());
				preparedStmt.setDouble(7, controle.getTotalFaturado());
				preparedStmt.setDouble(8, controle.getTotalSE());
				preparedStmt.setDouble(9, controle.getTotalLyra());
				preparedStmt.setDouble(10, controle.getTotalGSurf());
				preparedStmt.execute();
				conexao.close();
			}else{
				conexao = Pool.getConnection();
				PreparedStatement preparedStmt;
				preparedStmt = conexao.prepareStatement(UPDATE);
				preparedStmt.setString(1, controle.getNomePlanilha());
				preparedStmt.setInt(2, controle.getLinhasImportadas());
				preparedStmt.setInt(3, controle.getErros());
				preparedStmt.setTimestamp(4, controle.getDataImportacao());
				preparedStmt.setInt(5, controle.getLicencaNumLoja());
				preparedStmt.setDouble(6, controle.getTotalFaturado());
				preparedStmt.setDouble(7, controle.getTotalSE());
				preparedStmt.setDouble(8, controle.getTotalLyra());
				preparedStmt.setDouble(9, controle.getTotalGSurf());
				preparedStmt.setInt(10, controle.getIdcontrolPlanilha());
				preparedStmt.execute();
				conexao.close();
			}
						
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
		return true;		
	}
	

	public ControlPlanilhasEntity buscar(String planilha) throws SQLException {
		ControlPlanilhasEntity cpe = null;
		conexao = Pool.getConnection();
		Statement st = conexao.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM controlPlanilha WHERE nomePlanilha LIKE '"+ planilha +"';");
		if (rs.next()) {      
			cpe = new ControlPlanilhasEntity();
			cpe.setIdcontrolPlanilha(rs.getInt("idcontrolPlanilha"));
			cpe.setNomePlanilha(rs.getString("nomePlanilha"));
			cpe.setLinhasImportadas(rs.getInt("linhasImportadas"));
			cpe.setErros(rs.getInt("erros"));
			cpe.setDataImportacao(rs.getTimestamp("dataImportacao"));
			cpe.setLicencaNumLoja(rs.getInt("licencaNumLoja"));
			cpe.setTotalFaturado(rs.getDouble("totalFaturado"));
			cpe.setTotalSE(rs.getDouble("totalSE"));
			cpe.setTotalLyra(rs.getDouble("totalLyra"));
			cpe.setTotalGSurf(rs.getDouble("totalGSurf"));
		}
		conexao.close();
		return cpe;
	}
	public ControlPlanilhasEntity buscar(int id) throws SQLException {
		ControlPlanilhasEntity cpe = null;
		conexao = Pool.getConnection();
		Statement st = conexao.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM controlPlanilha WHERE idcontrolPlanilha= "+ id +";");
		if (rs.next()) {      
			cpe = new ControlPlanilhasEntity();
			cpe.setIdcontrolPlanilha(rs.getInt("idcontrolPlanilha"));
			cpe.setNomePlanilha(rs.getString("nomePlanilha"));
			cpe.setLinhasImportadas(rs.getInt("linhasImportadas"));
			cpe.setErros(rs.getInt("erros"));
			cpe.setDataImportacao(rs.getTimestamp("dataImportacao"));
			cpe.setLicencaNumLoja(rs.getInt("licencaNumLoja"));
			cpe.setTotalFaturado(rs.getDouble("totalFaturado"));
			cpe.setTotalSE(rs.getDouble("totalSE"));
			cpe.setTotalLyra(rs.getDouble("totalLyra"));
			cpe.setTotalGSurf(rs.getDouble("totalGSurf"));
		}
		conexao.close();
		return cpe;
	}
	public List<ControlPlanilhasEntity> listar() throws SQLException {
		List<ControlPlanilhasEntity> lista = new ArrayList<ControlPlanilhasEntity>();
		ControlPlanilhasEntity cpe = null;
		conexao = Pool.getConnection();
		Statement st = conexao.createStatement();
		ResultSet rs = st.executeQuery("SELECT * FROM controlPlanilha;");
		if (rs.next()) {   
			do{
				cpe = new ControlPlanilhasEntity();
				cpe.setIdcontrolPlanilha(rs.getInt("idcontrolPlanilha"));
				cpe.setNomePlanilha(rs.getString("nomePlanilha"));
				cpe.setLinhasImportadas(rs.getInt("linhasImportadas"));
				cpe.setErros(rs.getInt("erros"));
				cpe.setDataImportacao(rs.getTimestamp("dataImportacao"));
				cpe.setLicencaNumLoja(rs.getInt("licencaNumLoja"));
				cpe.setTotalFaturado(rs.getDouble("totalFaturado"));
				cpe.setTotalSE(rs.getDouble("totalSE"));
				cpe.setTotalLyra(rs.getDouble("totalLyra"));
				cpe.setTotalGSurf(rs.getDouble("totalGSurf"));
				lista.add(cpe);
			}while(rs.next());
		}
		conexao.close();
		return lista;
	}

	public ControlPlanilhasEntity buscarPlan(String planilha) throws SQLException {
		ControlPlanilhasEntity cpe = null;
		conexao = Pool.getConnection();
		Statement st = conexao.createStatement();
		ResultSet rs = st.executeQuery("SELECT cp.idcontrolPlanilha, cp.nomePlanilha, cp.linhasImportadas, cp.erros, cp.dataImportacao, cp.licencaNumLoja, cp.totalFaturado, cp.totalSE, cp.totalLyra, cp.totalGSurf FROM controlPlanilha as cp INNER JOIN erroPlanilha as ep ON cp.idcontrolPlanilha = ep.controlPlanilha_idcontrolPlanilha WHERE cp.nomePlanilha LIKE '"+ planilha +"';");
		if (rs.next()) {      
			cpe = new ControlPlanilhasEntity();
			cpe.setIdcontrolPlanilha(rs.getInt("idcontrolPlanilha"));
			cpe.setNomePlanilha(rs.getString("nomePlanilha"));
			cpe.setLinhasImportadas(rs.getInt("linhasImportadas"));
			cpe.setErros(rs.getInt("erros"));
			cpe.setDataImportacao(rs.getTimestamp("dataImportacao"));
			cpe.setLicencaNumLoja(rs.getInt("licencaNumLoja"));
			cpe.setTotalFaturado(rs.getDouble("totalFaturado"));
			cpe.setTotalSE(rs.getDouble("totalSE"));
			cpe.setTotalLyra(rs.getDouble("totalLyra"));
			cpe.setTotalGSurf(rs.getDouble("totalGSurf"));

		}
		conexao.close();
		return cpe;
	}

}
